<?php
/**
 * Created by PhpStorm.
 * User: DELL
 * Date: 2018/3/21
 * Time: 19:09
 */

namespace app\admin\controller;
use app\admin\model\Course as CourseModel;
use app\admin\model\CourseSign;
use app\home\model\Gate;
use app\home\model\Stage;
use app\home\model\Xuexiao;
use com\wechat\TPWechat;
use think\Config;
use app\home\model\WechatUser;
use think\Db;
use think\Request;
/**
 * Class Course
 * @package app\admin\controller
 * 课表控制器
 */
class Course extends Admin
{
    /**
     * @return mixed
     * 首页
     */
    public function index(){
        $place = input('place');
        $time = input('time');
        $name = input('name');
        $class = input('class');
        $start = strtotime($time);
        $end = strtotime($time)+60*60*24;
        if($class){
            // 搜索班型
            $Class = Db::name('skbx')->where(['banxing' => ['like','%'.$class.'%']])->field('id')->find();
            $cla = $Class['id'];
            if ($time){
                $map = array(
                    'status' => array('egt',0),
                    'place' => ['like','%'.$place.'%'],
                    'project_name' => ['like','%'.$name.'%'],
                    'start_time' => ['between',[$start,$end]],
                    'class' => $cla
                );
            }else{
                $map = array(
                    'status' => array('egt',0),
                    'place' => ['like','%'.$place.'%'],
                    'project_name' => ['like','%'.$name.'%'],
                    'class' => $cla
                );
            }
        }else{
            if ($time){
                $map = array(
                    'status' => array('egt',0),
                    'place' => ['like','%'.$place.'%'],
                    'project_name' => ['like','%'.$name.'%'],
                    'start_time' => ['between',[$start,$end]],
                );
            }else{
                $map = array(
                    'status' => array('egt',0),
                    'place' => ['like','%'.$place.'%'],
                    'project_name' => ['like','%'.$name.'%'],
                );
            }
        }
        $list = $this->lists('Course',$map,['start_time' => 'desc']);
        $this->assign('list',$list);
        return $this->fetch();
    }
    /**
     * 添加
     */
    public function edit(){
        $Course = new CourseModel();
        if (IS_POST){
            $data = input('post.');
            $data['school_id'] = json_encode($data['school_id']);
            $data['start_time'] = strtotime($data['start_time']);
            $data['end_time'] = strtotime($data['end_time']);
            $data['order'] = $data['country_id'].$data['city_id'].$data['education_id'].$data['place_id'].$data['name_id'].$data['project_id'].substr($data['subject_id'],2).$data['stage_id'].substr($data['module_id'],2).$data['time'].$data['teacher_id'].substr(date('Y',$data['start_time']),2).date('m',$data['start_time']).date('d',$data['start_time']);
            if ($data['id']){
                // 修改
                $res = $Course->save($data,['id' => $data['id']]);
            }else{
                // 添加
                unset($data['id']);
                $res = $Course->save($data);
            }
            if ($res){
                return $this->success('操作成功',Url('Course/index'));
            }else{
                return $this->error('操作失败');
            }
        }else{
            $id = input('id');
            $info = [];
            $is = 0;
            if ($id){
                $info = $Course->get($id);
                $info['school_id'] = json_decode($info['school_id']);
                $Sign = CourseSign::where('c_id',$id)->find();
                if ($Sign){
                    $is = 1;
                }
            }
            // 国家
            $country = Db::name('country')->field('num,name')->select();
            $this->assign('country',$country);
            // 省份
            $city = Db::name('province')->field('num,province')->select();
            $this->assign('city',$city);
            // 分校
            $place = Db::name('fenxiao')->field('num,name')->select();
            $this->assign('place',$place);
            // 学历
            $education = Db::name('xueli')->field('num,name')->select();
            $this->assign('education',$education);
            // 项目
            $gate = Db::name('xm')->select();
            foreach ($gate as $key => $value){
                if (empty($value['zxm_name'])){
                    $gate[$key]['num'] = $value['xm_num']."00";
                }else{
                    $gate[$key]['num'] = $value['xm_num'].$value['zxm_num'];
                }
            }
            $this->assign('gate',$gate);
            // 科目
            $subject = Db::name('kemu')->select();
            $this->assign('subject',$subject);
            // 辅导阶段
            $stage = Db::name('jieduan')->select();
            $this->assign('stage',$stage);
            // 课程名称
            $class = Db::name('classname')->select();
            $this->assign('name',$class);
            // 老师
            $teacher = Db::name('skteacher')->order('name')->field('name,num')->select();
            $this->assign('teacher',$teacher);
            // 模块
            $module = Db::name('mokuai')->field('id,kemu,mokuai,num')->select();
            array_push($module,['id' => 0,'kemu' => '','mokuai' => '','num' => "0000"]);
            $this->assign('module',$module);
            // 授课学校
            $school = Xuexiao::where(['dq_id' => ['neq',0]])->order('dx')->field('dx_id,dx')->select();
            $this->assign('school',$school);
            // 班型
            $class = Db::name('skbx')->order('id','asc')->field('id,banxing')->select();
            $this->assign('class',$class);
            $this->assign('msg',$info);
            $this->assign('is',$is);
            return $this->fetch();
        }
    }
    /**
     * 查看
     */
    public function scan(){
        $Course = new CourseModel();
        $id = input('id');
        if (empty($id)){
            return $this->error('系统参数错误');
        }
        $info = $Course->get($id);
        $info['school_id'] = json_decode($info['school_id']);
        // 项目
        $gate = Gate::where('')->order('id','asc')->select();
        $this->assign('gate',$gate);
        // 辅导阶段
        $stage = Stage::where('')->order('id')->select();
        $this->assign('stage',$stage);
        // 授课学校
        $school = Xuexiao::where(['dq_id' => ['neq',0]])->order('dx')->select();
        // 班型
        if ($info['class'] == 0){
            $class = "--暂无--";
        }else{
            $class = Db::name('skbx')->where('id',$info['class'])->value('banxing');
        }
        $info['class'] = $class;
        $this->assign('school',$school);
        $this->assign('msg',$info);
        $this->assign('id',$id);
        return $this->fetch();
    }
    /**
     * 删除
     */
    public function del(){
        $id = input('id');
        if (empty($id)){
            return $this->error('系统参数错误');
        }
        $Course = new CourseModel();
        $res = $Course->where('id',$id)->delete();
        if ($res){
            if (CourseSign::where('c_id',$id)->find()){
                CourseSign::where('c_id',$id)->delete();
            }
            return $this->success('删除成功');
        }else{
            return $this->error('删除失败');
        }
    }
    /**
     * 学员分配
     */
    public function student(){
        $id = input('id');
        if (empty($id)){
            return $this->error('系统参数错误');
        }
        $CourseSign = new CourseSign();
        $time = CourseModel::where('id',$id)->value('end_time');
        $is = 0;
        if ($time <= time()){
            // 已经截止 无法分配学员
            $is = 1;
        }
        $vip = input('vip');
        $type = input('type');
        $party = input('party');  // 年级
        $years = date('Y');
        $Year = [0];
        while (count($Year) <= 5){
            $Year[] = $years;
            $years += 1;
        }
        $this->assign('years',$Year);
        if (empty($party)){
            $party = 0;
        }
        $school = CourseModel::where('id',$id)->value('school_id');
        $list = [];
        if ($time < time()){
            //已经结束
            $old = $CourseSign->where(['status' => 0,'c_id' => $id])->field('id,openid')->select();
            if (!empty($old)){
                $ar1 = [];
                $ar2 = [];
                foreach ($old as $value){
                    array_push($ar1,$value['id']);
                    array_push($ar2,$value['openid']);
                }
                if ($ar1){
                    $CourseSign->where(['id' => ['in',$ar1]])->update(['status' => 3]);
                }
                if ($ar2){
                    WechatUser::where(['openid' => ['in',$ar2]])->setDec('score',3);
                }
            }
            if (substr($vip,0,1) === "@"){
                $vipp = substr($vip,1);
                if ($party == 0){
                    $map = ['a.name' => ['like', "%$vipp%"]];
                }else{
                    $map = ['a.name' => ['like', "%$vipp%"],'party' => $party];
                }
            }else{
                if ($party == 0){
                    $map = ['a.order_id' => ['like', "%$vip%"]];
                }else{
                    $map = ['a.order_id' => ['like', "%$vip%"],'party' => $party];
                }
            }
            if (empty($type) || $type == 1){  // 全部
                $list = Db::table('pb_wechat_user')
                    ->alias('a')
                    ->join('pb_course_sign b',"a.openid = b.openid")
                    ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                    ->where('b.c_id',$id)
                    ->where($map)
                    ->order('a.school_id,a.order_id','desc')
                    ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
            }elseif ($type == 4){  // 正常
                $list = Db::table('pb_wechat_user')
                    ->alias('a')
                    ->join('pb_course_sign b',"a.openid = b.openid")
                    ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                    ->where('b.c_id',$id)
                    ->where('b.status','eq',1)
                    ->where($map)
                    ->order('a.school_id,a.order_id','desc')
                    ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
            }elseif ($type == 5){  // 迟到
                $list = Db::table('pb_wechat_user')
                    ->alias('a')
                    ->join('pb_course_sign b',"a.openid = b.openid")
                    ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                    ->where('b.c_id',$id)
                    ->where('b.status','eq',2)
                    ->where($map)
                    ->order('a.school_id,a.order_id','desc')
                    ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
            }elseif ($type == 6){  //旷课
                $list = Db::table('pb_wechat_user')
                    ->alias('a')
                    ->join('pb_course_sign b',"a.openid = b.openid")
                    ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                    ->where('b.c_id',$id)
                    ->where('b.status','eq',3)
                    ->where($map)
                    ->order('a.school_id,a.order_id','desc')
                    ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
            }elseif ($type == 7){  // 请假
                $list = Db::table('pb_wechat_user')
                    ->alias('a')
                    ->join('pb_course_sign b',"a.openid = b.openid")
                    ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                    ->where('b.c_id',$id)
                    ->where('b.status','eq',4)
                    ->where($map)
                    ->order('a.school_id,a.order_id','desc')
                    ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
            }
        }else{
            if(in_array(0,json_decode($school))){
                // 总部所有人
                $class = CourseModel::where('id',$id)->value('class');
                if ($class == 0){
                    // 没有选择班型
                    if (substr($vip,0,1) === "@"){
                        $vipp = substr($vip,1);
                        if ($party == 0){
                            $map = ['a.review_status' => 1,'a.role' => 0,'a.name' => ['like', "%$vipp%"]];
                        }else{
                            $map = ['a.review_status' => 1,'a.role' => 0,'a.name' => ['like', "%$vipp%"],'party' => $party];
                        }
                    }else{
                        if ($party == 0){
                            $map = ['a.review_status' => 1,'a.role' => 0,'a.order_id' => ['like', "%$vip%"]];
                        }else{
                            $map = ['a.review_status' => 1,'a.role' => 0,'a.order_id' => ['like', "%$vip%"],'party' => $party];
                        }
                    }
                    if (empty($type) || $type == 1){  // 全部
                        $list = Db::table('pb_wechat_user')
                            ->alias('a')
                            ->where($map)
                            ->field('a.openid,a.id,a.name,a.school_id,a.order_id')
                            ->order('a.school_id,a.order_id','desc')
                            ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                    }elseif ($type == 2){  // 已分配
                        $list = Db::table('pb_wechat_user')
                            ->alias('a')
                            ->join('pb_course_sign b',"a.openid = b.openid")
                            ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                            ->where('b.c_id',$id)
                            ->where('b.status','eq',0)
                            ->where($map)
                            ->order('a.school_id,a.order_id','desc')
                            ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                    }elseif ($type == 3){  // 未分配
                        $arr = CourseSign::where(['c_id' => $id,'status' => ['egt',0]])->column('openid');
                        if (empty($arr)){
                            $list = Db::table('pb_wechat_user')
                                ->alias('a')
                                ->where($map)
                                ->field('a.openid,a.id,a.name,a.school_id,a.order_id')
                                ->order('a.school_id,a.order_id','desc')
                                ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                        }else{
                            $list = Db::table('pb_wechat_user')
                                ->alias('a')
                                ->where(['a.openid' => ['notin',$arr]])
                                ->where($map)
                                ->order('a.school_id,a.order_id','desc')
                                ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                        }
                    }
                }else{
                    // 有班型  class
                    $users = Db::name('class')->where('kid',$class)->group('openid')->column('openid');
                    if (empty($users)){
                        $users = ['***'];
                    }
                    if (substr($vip,0,1) === "@"){
                        $vipp = substr($vip,1);
                        if ($party == 0){
                            $map = ['a.openid' => ['in',$users],'a.review_status' => 1,'a.role' => 0,'a.name' => ['like', "%$vipp%"]];
                        }else{
                            $map = ['a.openid' => ['in',$users],'a.review_status' => 1,'a.role' => 0,'a.name' => ['like', "%$vipp%"],'party' => $party];
                        }
                    }else{
                        if ($party == 0){
                            $map = ['a.openid' => ['in',$users],'a.review_status' => 1,'a.role' => 0,'a.order_id' => ['like', "%$vip%"]];
                        }else{
                            $map = ['a.openid' => ['in',$users],'a.review_status' => 1,'a.role' => 0,'a.order_id' => ['like', "%$vip%"],'party' => $party];
                        }
                    }
                    if (empty($type) || $type == 1){  // 全部
                        $list = Db::table('pb_wechat_user')
                            ->alias('a')
                            ->where($map)
                            ->field('a.openid,a.id,a.name,a.school_id,a.order_id')
                            ->order('a.school_id,a.order_id','desc')
                            ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                    }elseif ($type == 2){  // 已分配
                        $list = Db::table('pb_wechat_user')
                            ->alias('a')
                            ->join('pb_course_sign b',"a.openid = b.openid")
                            ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                            ->where('b.c_id',$id)
                            ->where('b.status','eq',0)
                            ->where($map)
                            ->order('a.school_id,a.order_id','desc')
                            ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                    }elseif ($type == 3){  // 未分配
                        $arr = CourseSign::where(['c_id' => $id,'status' => ['egt',0]])->column('openid');
                        if (empty($arr)){
                            $list = Db::table('pb_wechat_user')
                                ->alias('a')
                                ->where($map)
                                ->field('a.openid,a.id,a.name,a.school_id,a.order_id')
                                ->order('a.school_id,a.order_id','desc')
                                ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                        }else{
                            $list = Db::table('pb_wechat_user')
                                ->alias('a')
                                ->where(['a.openid' => ['notin',$arr]])
                                ->where($map)
                                ->order('a.school_id,a.order_id','desc')
                                ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                        }
                    }
                }
            }else{
                if (substr($vip,0,1) === "@"){
                    $vipp = substr($vip,1);
                    if ($party == 0){
                        $map = ['a.school_id' => ['in',json_decode($school)],'a.review_status' => 1,'a.role' => 0,'a.name' => ['like', "%$vipp%"]];
                    }else{
                        $map = ['a.school_id' => ['in',json_decode($school)],'a.review_status' => 1,'a.role' => 0,'a.name' => ['like', "%$vipp%"],'party' => $party];
                    }
                }else{
                    if ($party == 0){
                        $map = ['a.school_id' => ['in',json_decode($school)],'a.review_status' => 1,'a.role' => 0,'a.order_id' => ['like', "%$vip%"]];
                    }else{
                        $map = ['a.school_id' => ['in',json_decode($school)],'a.review_status' => 1,'a.role' => 0,'a.order_id' => ['like', "%$vip%"],'party' => $party];
                    }
                }
                if (empty($type) || $type == 1){  // 全部
                    $list = Db::table('pb_wechat_user')
                        ->alias('a')
                        ->where($map)
                        ->field('a.openid,a.id,a.name,a.school_id,a.order_id')
                        ->order('a.school_id,a.order_id','desc')
                        ->paginate(10,false,['query' => ['id'=> $id,'type' => $type,'vip' => $vip,'party' => $party]]);
                }elseif ($type == 2){  // 已分配
                    $list = Db::table('pb_wechat_user')
                        ->alias('a')
                        ->join('pb_course_sign b',"a.openid = b.openid")
                        ->field('a.openid,a.id,a.name,a.school_id,a.order_id,b.status')
                        ->where('b.c_id',$id)
                        ->where('b.status','eq',0)
                        ->where($map)
                        ->order('a.school_id,a.order_id','desc')
                        ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                }elseif ($type == 3){  // 未分配
                    $arr = CourseSign::where(['c_id' => $id,'status' => ['egt',0]])->column('openid');
                    if (empty($arr)){
                        $list = Db::table('pb_wechat_user')
                            ->alias('a')
                            ->where($map)
                            ->field('a.openid,a.id,a.name,a.school_id,a.order_id')
                            ->order('a.school_id,a.order_id','desc')
                            ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                    }else{
                        $list = Db::table('pb_wechat_user')
                            ->alias('a')
                            ->where(['a.openid' => ['notin',$arr]])
                            ->where($map)
                            ->order('a.school_id,a.order_id','desc')
                            ->paginate(10,false,['query' => ['type' => $type,'vip' => $vip,'party' => $party]]);
                    }
                }
            }
        }
        $this->assign('list',$list);
        $this->assign('id',$id);
        $this->assign('is',$is);
        $this->assign('type',$type);
        $this->assign('party',$party);
        return $this->fetch();
    }
    /**
     * 批量操作
     */
    public function all(){
        $data = input('post.');
        $id = $data['id'];
        $users = $data['users']; // 数组
        $type = $data['type']; // 1 确认 2 取消
        foreach ($users as $value){
            $Sign = CourseSign::where(['c_id' => $id,'openid' => $value])->find();
            if ($Sign){
                // 已经确认
                if ($type == 2){
                    // 取消
                    CourseSign::where(['c_id' => $id,'openid' => $value])->delete();
                }
            }else{
                // 未确认
                if ($type == 1){
                    // 确认
                    CourseSign::create(['c_id' => $id,'openid' => $value]);
                }
            }
        }
        return $this->success('操作成功');
    }
    /**
     * 单个提醒
     */
    public function call(){
        $openid = input('openid');
        if (empty($openid)){
            return $this->error('系统参数错误');
        }
        $con = '【课表更新了】
老师已为你最新分配了课表，点击<a href="http://mp.csppat.com/DDDeducation/public/index.php/home/Sign/index">我的课表</a>可快速查看，注意按时上课签到哦~~';
        $send = array(
            "content" => $con
        );
        $Wechat = new TPWechat(Config::get('party'));
        $data = array(
            "touser" => $openid,
            "msgtype" => "text",
            "text" => $send
        );
        $res = $Wechat->sendCustomMessage($data);
        if ($res){
            return $this->success('提醒成功');
        }else{
            return $this->error('提醒失败');
        }
    }
    /**
     * 批量提醒
     */
    public function remind(){
        $id = input('id');
        if (empty($id)){
            return $this->error('系统参数错误');
        }
        $openids = CourseSign::where(['c_id' => $id,'status' => 0])->column('openid');
        if (empty($openids)){
            return $this->error('请先分配学员');
        }
        $con = '【课表更新了】
老师已为你最新分配了课表，点击<a href="http://mp.csppat.com/DDDeducation/public/index.php/home/Sign/index">我的课表</a>可快速查看，注意按时上课签到哦~~';
        $send = array(
            "content" => $con
        );
        $Wechat = new TPWechat(Config::get('party'));
        $data = array(
            "touser" => $openids,
            "msgtype" => "text",
            "text" => $send
        );
        $res = $Wechat->sendMassMessage($data);
        if ($res){
            return $this->success('提醒成功');
        }else{
            return $this->error('提醒失败');
        }
    }
    /**
     * 导出 Excel
     */
    public function outpaper(){
        vendor("phpexcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory");
        vendor('phpexcel.Classes.PHPExcel');
        $id = input('id');
        if(empty($id)){
            return $this->error('系统参数错误');
        }
        $Course = new CourseModel();
        $info = $Course->get($id);
        $city = $info['city']?$info['city']:'暂无'; // 省份
        $fx = $info['place']?$info['place']:'暂无';  // 分校
        $xl = $info['education']?$info['education']:'暂无';  // 学历
        $kcmc = $info['name']?$info['name']:'暂无';  //课程名称
        $xm = $info['project_name']?$info['project_name']:'暂无';  //项目
        $km = $info['subject']?$info['subject']:'暂无';  //科目
        $fdjd = $info['stage_name']?$info['stage_name']:'暂无';  //辅导阶段
        $mk = $info['module']?$info['module']:'暂无';  // 模块
        $kc = $info['time_name']?$info['time_name']:'暂无';  // 课次
        if ($info['class'] == 0){
            $bx = "暂无";
        }else{
            $bx = Db::name('skbx')->where('id',$info['class'])->value('banxing'); // 班型
        }
        $skls = $info['teacher']?$info['teacher']:'暂无';  //授课老师
        $schools = json_decode($info['school_id']);
        $skxx = '';  // 授课学校
        foreach ($schools as $value){
            if ($value == 0){
                $skxx .= '点对点总部 /';
            }else{
                $tep = Xuexiao::where('dx_id',$value)->value('dx');
                $skxx .= iconv("gbk", "utf-8//ignore",$tep).' /';
            }
        }
        $skdd = $info['address']?$info['address']:'暂无';  //授课地点
        $sksj = date('Y/m/d H:i',$info['start_time']).'—'.date('Y/m/d H:i',$info['end_time']);
        $sknr = $info['content']?$info['content']:'暂无'; // 授课内容
        //引用PHPExcel
        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        //给当前活动sheet填充数据，数据填充是按顺序一行一行填充的，假如想给A1留空，可以直接setCellValue(‘A1’,’’);
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
        $title = ['省份','分校','学历','课程名称','项目','科目','辅导阶段','模块','课次','班型','授课老师','授课学校','授课地点','上课时间','授课内容']; //对应Excel标题
        //标题输出
        foreach ($title as $k => $v) {
            $PHPSheet->setCellValue($cellName[$k].'1',$v);
            $PHPSheet->getStyle($cellName[$k].'1')->getFont()->setSize(15);
            $PHPSheet->getColumnDimension($cellName[$k])->setWidth(25);
            $PHPSheet->getRowDimension(1)->setRowHeight(25);
            $PHPSheet->getStyle($cellName[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        $PHPSheet->setCellValue($cellName[0] . 2, $city)
            ->setCellValue($cellName[1] . 2, $fx)
            ->setCellValue($cellName[2] . 2, $xl)
            ->setCellValue($cellName[3] . 2, $kcmc)
            ->setCellValue($cellName[4] . 2, $xm)
            ->setCellValue($cellName[5] . 2, $km)
            ->setCellValue($cellName[6] . 2, $fdjd)
            ->setCellValue($cellName[7] . 2, $mk)
            ->setCellValue($cellName[8] . 2, $kc)
            ->setCellValue($cellName[9] . 2, $bx)
            ->setCellValue($cellName[10] . 2, $skls)
            ->setCellValue($cellName[11] . 2, $skxx)
            ->setCellValue($cellName[12] . 2, $skdd)
            ->setCellValue($cellName[13] . 2, $sksj)
            ->setCellValue($cellName[14] . 2, $sknr);
        //内容输出
        $cellNames = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
        $titles = ['姓名','VIP账号','所属学校','签到情况']; //对应Excel标题
        foreach ($titles as $k => $v) {
            $PHPSheet->setCellValue($cellNames[$k].'5',$v);
            $PHPSheet->getStyle($cellNames[$k].'5')->getFont()->setSize(15);
            $PHPSheet->getColumnDimension($cellNames[$k])->setWidth(25);
            $PHPSheet->getRowDimension(1)->setRowHeight(25);
            $PHPSheet->getStyle($cellNames[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
        $list = CourseSign::where(['c_id' => $id,'status' => ['egt',0]])->select();
        foreach ($list as $key => $value){
            $User = WechatUser::where(['openid' => $value['openid']])->find();
            $name = $User['name'];
            $vip = $User['order_id'];
            $sch = get_school($User['school_id']);
            $sta = getPres($value['openid'],$id);
            $w = $key + 6;
            $PHPSheet->setCellValue($cellNames[0] . $w, $name)
                ->setCellValue($cellNames[1] . $w, $vip)
                ->setCellValue($cellNames[2] . $w, $sch)
                ->setCellValue($cellNames[3] . $w, $sta);
        }
        $fileName = '课表签到记录'.date('Y年m月d日',$info['start_time']);
        // 设置下载头部
        $ua = $_SERVER["HTTP_USER_AGENT"];
        $encoded_filename = urlencode($fileName);
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Type: application/octet-stream');
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="'.$encoded_filename.'.xls');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\''.$fileName.'.xls');
        } else {
            header('Content-Disposition: attachment; filename="'.$fileName.'.xls');
        }
        header('Content-Type: application/vnd.ms-excel;');
        header('Cache-Control: max-age=0');
        // 输出下载文件
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
    /**
     * 通讯录excel导入
     */
    public function inserExcel(){
        //引用PHPExcel
        vendor("phpexcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory");
        vendor('phpexcel.Classes.PHPExcel');
        vendor('phpexcel.Classes.PHPExcel.Reader.Excel5');
        vendor('phpexcel.Classes.PHPExcel.Reader.Excel2007');
        //获取表单上传文件
        $file = request() ->file('excel');
        $Info = $file ->getInfo();
        $Ext = explode('.',$Info['name']);
        $ext = $Ext[1];
        $info = $file ->move(ROOT_PATH . 'public' . DS . 'uploads');//上传之后移动地址
        if ($info) {
            $exclePath = $info->getPathName();  //上传文件的地址
            if ($ext == 'xlsx'){
                $objReader = \PHPExcel_IOFactory::createReader('excel2007');
            }else{
                $objReader = \PHPExcel_IOFactory::createReader('Excel5');
            }
            $obj_PHPExcel = $objReader ->load($exclePath, $encode = 'utf-8');  //加载文件内容,编码utf-8
            $excel_array = $obj_PHPExcel ->getsheet(0) ->toArray();   //转换为数组格式
            array_shift($excel_array);  //删除第一个数组(标题);
            $result = $this ->add_excel($excel_array);
            unlink($exclePath);//完成后删除该文件
            return $result;
        } else {
            return $file ->getError();
        }
    }
    /**
     * 通讯录导入处理函数
     */
    public function add_excel($data){
        $user = new CourseModel();
        $sum = 0;//记录新增用户记录
        $all = array();
        foreach($data as $k => $v){
            if (empty($v[0]) || empty($v[1]) || empty($v[3]) || empty($v[4]) || empty($v[5]) || empty($v[6]) || empty($v[7]) || empty($v[8]) || empty($v[9]) || empty($v[10]) || empty($v[16]) || empty($v[13]) || empty($v[14]) || empty($v[15])){
                return ['code' => 0, 'msg' => '第' . ($k + 2) . '行缺少字段值!'];
            }else{
                // 监测是否有值  数据库中有值则可以添加  无值不能添加
                $info = [];
                // 国家
                $Country = Db::name('country')->where('num',$v[0])->field('name')->find();
                if ($Country){
                    // 值正确
                    $info['country'] = $Country['name'];
                    $info['country_id'] = $v[0];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *国家* 字段值错误!'];
                }
                // 省份
                $City = Db::name('province')->where('num',$v[1])->field('province')->find();
                if ($City){
                    // 值正确
                    $info['city'] = $City['province'];
                    $info['city_id'] = $v[1];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *省份* 字段值错误!'];
                }
                // 分校
                $Place = Db::name('fenxiao')->where('num',$v[2])->field('name')->find();
                if ($Place){
                    $info['place'] = $Place['name'];
                    $info['place_id'] = $v[2];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *分校* 字段值错误!'];
                }
                // 学历
                $Education = Db::name('xueli')->where('num',$v[3])->field('name')->find();
                if ($Education){
                    $info['education'] = $Education['name'];
                    $info['education_id'] = $v[3];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *学历* 字段值错误!'];
                }
                // 项目  项目子项目
                $xm = strpos($v[4],'/');
                if($xm !== false){
                    // 有 /
                    $Xm = explode('/',$v[4]); // 数组
                    $Gate = Db::name('xm')->where('xm_name',$Xm[0])->field('xm_num')->find();
                    if ($Gate){
                        $Gates = Db::name('xm')->where('zxm_name',$Xm[1])->field('zxm_num')->find();
                        if ($Gates){
                            $info['project_name'] = $Xm[0]."——".$Xm[1];
                            $info['project_id'] = $Gate['xm_num'].$Gates['zxm_num'];
                        }else{
                            return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *项目* 字段值错误!'];
                        }
                    }else{
                        // 值错误
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *项目* 字段值错误!'];
                    }
                }else{
                    // 无  /
                    $Gate = Db::name('xm')->where('xm_name',$v[4])->field('xm_num')->find();
                    if ($Gate){
                        $info['project_name'] = $v[4]."——"."暂无";
                        $info['project_id'] = $Gate['xm_num'];
                    }else{
                        // 值错误
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *项目* 字段值错误!'];
                    }
                }
                // 科目
                $km = strpos($v[5],'/');
                if ($km !== false){
                    $Km = explode('/',$v[5]); // 数组
                    $Subject = Db::name('kemu')->where(['zxm_name' => $Km[0],'kemu' => $Km[1]])->field('num')->find();
                    if ($Subject) {
                        $info['subject'] = $Km[0] . "——" . $Km[1];
                        $info['subject_id'] = $Subject['num'];
                    }else{
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *科目* 字段值错误!'];
                    }
                }else{
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *科目* 字段值错误!'];
                }
                // 辅导阶段
                $Stage = Db::name('jieduan')->where('name',$v[6])->field('num')->find();
                if ($Stage){
                    $info['stage_name'] = $v[6];
                    $info['stage_id'] = $Stage['num'];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *辅导阶段* 字段值错误!'];
                }
                // 课程名称
                $Class = Db::name('classname')->where('name',$v[7])->field('num')->find();
                if ($Class){
                    $info['name'] = $v[7];
                    $info['name_id'] = $Class['num'];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *课程名称* 字段值错误!'];
                }
                // 老师 
                $Teacher = Db::name('skteacher')->where('name',$v[8])->field('num')->find();
                if ($Teacher){
                    $info['teacher'] = $v[8];
                    $info['teacher_id'] = $Teacher['num'];
                }else{
                    // 值错误
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *授课老师* 字段值错误!'];
                }
                // 模块
                $mk = strpos($v[9],'/');
                if ($mk !== false){
                    $Mk = explode('/',$v[9]); // 数组
                    $Module = Db::name('mokuai')->where(['kemu' => $Mk[0],'mokuai' => $Mk[1]])->field('num')->find();
                    if ($Module) {
                        $info['module'] = $Km[0] . "——" . $Km[1];
                        $info['module_id'] = $Module['num'];
                    }else{
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *模块* 字段值错误!'];
                    }
                }else{
                    // mokuai  為空
                    $Module = Db::name('mokuai')->where(['kemu' => $v[9]])->field('num')->find();
                    if ($Module) {
                        $info['module'] = $v[9] . "——" . "暂无";
                        $info['module_id'] = $Module['num'];
                    }else{
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *模块* 字段值错误!'];
                    }
                }
                // 课次 01
                $info['time'] = $v[10];
                switch ($v[10]){
                    case '01':
                        $info['time_name'] = "第一次";
                        break;
                    case '02':
                        $info['time_name'] = "第二次";
                        break;
                    case '03':
                        $info['time_name'] = "第三次";
                        break;
                    case '04':
                        $info['time_name'] = "第四次";
                        break;
                    case '05':
                        $info['time_name'] = "第五次";
                        break;
                    case '06':
                        $info['time_name'] = "第六次";
                        break;
                    case '07':
                        $info['time_name'] = "第七次";
                        break;
                    case '08':
                        $info['time_name'] = "第八次";
                        break;
                    case '09':
                        $info['time_name'] = "第九次";
                        break;
                    case '10':
                        $info['time_name'] = "第十次";
                        break;
                    case '11':
                        $info['time_name'] = "第十一次";
                        break;
                    case '12':
                        $info['time_name'] = "第十二次";
                        break;
                    case '13':
                        $info['time_name'] = "第十三次";
                        break;
                    case '14':
                        $info['time_name'] = "第十四次";
                        break;
                    case '15':
                        $info['time_name'] = "第十五次";
                        break;
                    case '16':
                        $info['time_name'] = "第十六次";
                        break;
                    case '17':
                        $info['time_name'] = "第十七次";
                        break;
                    case '18':
                        $info['time_name'] = "第十八次";
                        break;
                    case '19':
                        $info['time_name'] = "第十九次";
                        break;
                    case '20':
                        $info['time_name'] = "第二十次";
                        break;
                    case '21':
                        $info['time_name'] = "第二十一次";
                        break;
                    case '22':
                        $info['time_name'] = "第二十二次";
                        break;
                    case '23':
                        $info['time_name'] = "第二十三次";
                        break;
                    case '24':
                        $info['time_name'] = "第二十四次";
                        break;
                    case '25':
                        $info['time_name'] = "第二十五次";
                        break;
                    case '26':
                        $info['time_name'] = "第二十六次";
                        break;
                    case '27':
                        $info['time_name'] = "第二十七次";
                        break;
                    case '28':
                        $info['time_name'] = "第二十八次";
                        break;
                    case '29':
                        $info['time_name'] = "第二十九次";
                        break;
                    case '30':
                        $info['time_name'] = "第三十次";
                        break;
                    case '31':
                        $info['time_name'] = "第三十一次";
                        break;
                    case '32':
                        $info['time_name'] = "第三十二次";
                        break;
                    case '33':
                        $info['time_name'] = "第三十三次";
                        break;
                    case '34':
                        $info['time_name'] = "第三十四次";
                        break;
                    case '35':
                        $info['time_name'] = "第三十五次";
                        break;
                    case '36':
                        $info['time_name'] = "第三十六次";
                        break;
                    case '37':
                        $info['time_name'] = "第三十七次";
                        break;
                    case '38':
                        $info['time_name'] = "第三十八次";
                        break;
                    case '39':
                        $info['time_name'] = "第三十九次";
                        break;
                    case '40':
                        $info['time_name'] = "第四十次";
                        break;
                    case '41':
                        $info['time_name'] = "第四十一次";
                        break;
                    case '42':
                        $info['time_name'] = "第四十二次";
                        break;
                    case '43':
                        $info['time_name'] = "第四十三次";
                        break;
                    case '44':
                        $info['time_name'] = "第四十四次";
                        break;
                    case '45':
                        $info['time_name'] = "第四十五次";
                        break;
                    case '46':
                        $info['time_name'] = "第四十六次";
                        break;
                    case '47':
                        $info['time_name'] = "第四十七次";
                        break;
                    case '48':
                        $info['time_name'] = "第四十八次";
                        break;
                    case '49':
                        $info['time_name'] = "第四十九次";
                        break;
                    case '50':
                        $info['time_name'] = "第五十次";
                        break;
                    default:
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *课次* 字段值错误!'];
                }
                // 班型 没有为 0
                $info['class'] = 0;
                if ($v[11] !== 0){
                    $Classs = Db::name('skbx')->where('banxing',$v[11])->field('id')->find();
                    if ($Classs){
                        $info['class'] = $Classs['id'];
                    }else{
                        return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *班型* 字段值错误!'];
                    }
                }
                // 授课学校  单选/多选  **,**   0为点对点总部
                $str = strpos($v[12],'/');
                $tep = [];
                if ($str !== false){
                    // 多选
                    $Sch = explode('/',$v[12]); // 数组
                    foreach ($Sch as $val){
                        if ($val == "点对点总部"){
                            array_push($tep,0);
                        }else{
                            $School = Xuexiao::where(['dx' => iconv("UTF-8", "GBK//TRANSLIT", $val)])->field('dx_id')->find();
                            if (empty($School)){
                                return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *授课学校* 字段值错误!'];
                            }else{
                                array_push($tep,$School['dx_id']);
                            }
                        }
                    }
                }else{
                    // 单选
                    if ($v[12] == "点对点总部"){
                        array_push($tep,0);
                    }else{
                        $School = Xuexiao::where(['dx' => iconv("UTF-8", "GBK//TRANSLIT", $v[12])])->field('dx_id')->find();
                        if (empty($School)){
                            return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *授课学校* 字段值错误!'];
                        }else{
                            array_push($tep,$School['dx_id']);
                        }
                    }
                }
                $info['school_id'] = json_encode($tep);
                // 授课地点
                $info['address'] = $v[13];
                // 上课时间 开始 YYYY-mm-dd HH:ii:ss
                $start_time = strtotime($v[14]);
                if ($start_time){
                    $info['start_time'] = $start_time;
                }else{
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *开始时间* 字段值错误!'];
                }
                // 上课时间 结束 YYYY-mm-dd HH:ii:ss
                $end_time = strtotime($v[15]);
                if ($end_time){
                    $info['end_time'] = $end_time;
                }else{
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *结束时间* 字段值错误!'];
                }
                // 授课内容
                $info['content'] = $v[16];
                // 是否领资料 0/1
                if (in_array($v[17],[0,1])){
                    $info['receive'] = $v[17];
                }else{
                    return ['code' => 0, 'msg' => '第' . ($k + 2) . '行 *是否领资料* 字段值错误!'];
                }
                $info['order'] = $info['country_id'].$info['city_id'].$info['education_id'].$info['place_id'].$info['name_id'].$info['project_id'].substr($info['subject_id'],2).$info['stage_id'].substr($info['module_id'],2).$info['time'].$info['teacher_id'].substr(date('Y',$info['start_time']),2).date('m',$info['start_time']).date('d',$info['start_time']);
                array_push($all, $info);
                $sum++;
            }
        }
        //新增用户保存
        $user ->saveAll($all);
        return  $this ->success("导入成功,新增课表{$sum}个!");
    }
    /**
     * 签到
     */
    public function play(){
        $openid = input('openid');
        $id = input('id');
        $sta = input('sta'); // 状态 2 迟到 (-1) 3 旷课 (-3) 4 请假 (0)
        $score = 0;
        if ($sta == 2){
            $score = 1;
        }elseif ($sta == 3){
            $score = 3;
        }
        $Sign = CourseModel::where('id',$id)->field('start_time,end_time')->find();
        $res = CourseSign::where(['c_id'=> $id,'openid' => $openid])->update(['status' => 1,'create_time' => time(),'signin_time' => $Sign['start_time'],'signout_time' => $Sign['end_time']]);
        if ($res){
            WechatUser::where(['openid' => $openid])->setInc('score',$score);
            return $this->success('操作成功');
        }else{
            return $this->error('操作失败');
        }
    }
    /**
     * 批量 签到
     */
    public function plays(){
        $openid = input('openid/a');
        $id = input('id');
        $Sign = CourseModel::where('id',$id)->field('start_time,end_time')->find();
        $res = CourseSign::where(['c_id'=> $id,'openid' => ['in',$openid]])->update(['status' => 1,'create_time' => time(),'signin_time' => $Sign['start_time'],'signout_time' => $Sign['end_time']]);
        if ($res){
            WechatUser::where(['openid' => ['in',$openid]])->setInc('score',2);
            return $this->success('操作成功');
        }else{
            return $this->error('操作失败');
        }
    }
    /**
     * 导出 Excel  根据时间
     */
    public function outpapers(){
        vendor("phpexcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory");
        vendor('phpexcel.Classes.PHPExcel');
        $start = input('start');
        $end = input('end');
        if(empty($start) || empty($end)){
            return $this->error('系统参数错误');
        }
        //引用PHPExcel
        $PHPExcel = new \PHPExcel(); //实例化PHPExcel类，类似于在桌面上新建一个Excel表格
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        //给当前活动sheet填充数据，数据填充是按顺序一行一行填充的，假如想给A1留空，可以直接setCellValue(‘A1’,’’);
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
        $title = ['省份','学历','项目','分校编加名字','上课地点','课程名称','班型','上课科目','上课阶段','上课模块','上课模块课次','上课日期','上课星期','上课时间','上课老师','上课学员具体学校','上课人数','签到人数','请假人数','旷课人数','出勤率','提交测试卷情况','参加测试人数','提交成绩人数','测试完成率','测试平均分','测试最高分','测试最低分']; //对应Excel标题
        //标题输出
        foreach ($title as $k => $v) {
            $PHPSheet->setCellValue($cellName[$k].'1',$v);
            $PHPSheet->getStyle($cellName[$k].'1')->getFont()->setSize(13);
            $PHPSheet->getStyle($cellName[$k].'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            if($k < 17){
                $PHPSheet->getStyle($cellName[$k].'1')->getFill()->getStartColor()->setARGB('244696');
            }elseif ($k >= 17 && $k <= 21){
                $PHPSheet->getStyle($cellName[$k].'1')->getFill()->getStartColor()->setARGB('7CCD7C');
            }else{
                $PHPSheet->getStyle($cellName[$k].'1')->getFill()->getStartColor()->setARGB('f2eada');
            }
            $PHPSheet->getColumnDimension($cellName[$k])->setWidth(25);
            $PHPSheet->getRowDimension(1)->setRowHeight(20);
            $PHPSheet->getStyle($cellName[$k])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            $PHPSheet->getStyle($cellName[$k].'1')->applyFromArray(
                [
                    'font' => ['bold' => true],
                    'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]
                ]
            );
        }

        $Course = new CourseModel();
        $list = $Course->where(['start_time' => ['between',[strtotime($start),strtotime($end)+60*60*24]]])->order(['project_id' => 'asc','start_time' => 'asc'])->select();
        foreach ($list as $key => $value){
            $city = $value['city']?$value['city']:'暂无'; // 省份城市
            $xl = $value['education']?$value['education']:'暂无';  // 学历
            $xm = $value['project_name']?$value['project_name']:'暂无';  //项目
            $fx = $value['place']?$value['place']:'暂无';  // 分校
            $skdd = $value['address']?$value['address']:'暂无';  //授课地点
            $kcmc = $value['name']?$value['name']:'暂无';  //课程名称
            if ($value['class'] == 0){
                $bx = "暂无";
            }else{
                $bx = Db::name('skbx')->where('id',$value['class'])->value('banxing'); // 班型
            }
            $km = $value['subject']?$value['subject']:'暂无';  //科目
            $fdjd = $value['stage_name']?$value['stage_name']:'暂无';  //辅导阶段
            $mk = $value['module']?$value['module']:'暂无';  // 模块
            $kc = $value['time_name']?$value['time_name']:'暂无';  // 课次
            $sta = date('Y年m月d日',$value['start_time']);  // 上课日期
            $week = "空";
            switch (date('w',$value['start_time'])){
                case 0:
                    $week = "星期日";
                    break;
                case 1:
                    $week = "星期一";
                    break;
                case 2:
                    $week = "星期二";
                    break;
                case 3:
                    $week = "星期三";
                    break;
                case 4:
                    $week = "星期四";
                    break;
                case 5:
                    $week = "星期五";
                    break;
                case 6:
                    $week = "星期六";
                    break;
            }
            $sksj = date('H:i',$value['start_time']).'—'.date('H:i',$value['end_time']);
            $skls = $value['teacher']?$value['teacher']:'暂无';  //授课老师
            $schools = json_decode($value['school_id']);
            $skxx = '';  // 授课学校
            foreach ($schools as $val){
                if ($val == 0){
                    $skxx .= '点对点总部 /';
                }else{
                    $tep = Xuexiao::where('dx_id',$val)->value('dx');
                    $skxx .= iconv("gbk", "utf-8//ignore",$tep).' /';
                }
            }
            $sum = CourseSign::where(['c_id' => $value['id']])->count();
            $num1 = CourseSign::where(['c_id' => $value['id'],'status' => ['in',[1,2]]])->count();
            $num2 = CourseSign::where(['c_id' => $value['id'],'status' => 4])->count();
            $num3 = $sum-$num1-$num2;
            if ($sum == 0){
                $num4 = 0;
            }else{
                $num4 = round($num1 /$sum, 3) >1 ? '100%' : 100*round($num1 /$sum, 3).'%';
            }
            // 提交测试卷情况
            $paper = get_stage($value['id']);
            if ($paper == "暂无"){
                $paper_is = '否';
                $paper1 = 0;
                $paper2 = 0;
                $paper3 = 0;
                $paper4 = 0;
                $paper5 = 0;
                $paper6 = 0;
            }else{
                $paper_is = '是';
                $paper1 = $sum;
                $sql = "SELECT MAX(score) as score1,MIN(score) as score2,COUNT(*) as score3,AVG(score) as score4 FROM pb_test WHERE status = 0 AND code = ".$value['id'];
                $Info = Db::query($sql);
                $Info = $Info[0];
                $paper2 = $Info['score3'] ? $Info['score3'] : 0;
                if ($paper1 == 0){
                    $paper3 = 0;
                    $paper4 = 0;
                }else{
                    $paper3 = round($paper2 /$paper1, 3) >1 ? '100%' : 100*round($paper2 /$paper1, 3).'%';
                    $paper4 = $Info['score4'] ? round($Info['score4'], 2) : 0;
                }
                $paper5 = $Info['score1'] ? $Info['score1'] : 0;
                $paper6 = $Info['score2'] ? $Info['score2'] : 0;
            }
            $key = $key + 2;
            $PHPSheet->setCellValue($cellName[0] . $key, $city)
                ->setCellValue($cellName[1] . $key, $xl)
                ->setCellValue($cellName[2] . $key, $xm)
                ->setCellValue($cellName[3] . $key, $fx)
                ->setCellValue($cellName[4] . $key, $skdd)
                ->setCellValue($cellName[5] . $key, $kcmc)
                ->setCellValue($cellName[6] . $key, $bx)
                ->setCellValue($cellName[7] . $key, $km)
                ->setCellValue($cellName[8] . $key, $fdjd)
                ->setCellValue($cellName[9] . $key, $mk)
                ->setCellValue($cellName[10] . $key, $kc)
                ->setCellValue($cellName[11] . $key, $sta)
                ->setCellValue($cellName[12] . $key, $week)
                ->setCellValue($cellName[13] . $key, $sksj)
                ->setCellValue($cellName[14] . $key, $skls)
                ->setCellValue($cellName[15] . $key, $skxx)
                ->setCellValue($cellName[16] . $key, $sum)
                ->setCellValue($cellName[17] . $key, $num1)
                ->setCellValue($cellName[18] . $key, $num2)
                ->setCellValue($cellName[19] . $key, $num3)
                ->setCellValue($cellName[20] . $key, $num4)
                ->setCellValue($cellName[21] . $key, $paper_is)
                ->setCellValue($cellName[22] . $key, $paper1)
                ->setCellValue($cellName[23] . $key, $paper2)
                ->setCellValue($cellName[24] . $key, $paper3)
                ->setCellValue($cellName[25] . $key, $paper4)
                ->setCellValue($cellName[26] . $key, $paper5)
                ->setCellValue($cellName[27] . $key, $paper6);

        }
        $fileName = date('Y.m.d',strtotime($start)).'-'.date('Y.m.d',strtotime($end)).'周末小班课课表';
        // 设置下载头部
        $ua = $_SERVER["HTTP_USER_AGENT"];
        $encoded_filename = urlencode($fileName);
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Type: application/octet-stream');
        if (preg_match("/MSIE/", $ua)) {
            header('Content-Disposition: attachment; filename="'.$encoded_filename.'.xls');
        } else if (preg_match("/Firefox/", $ua)) {
            header('Content-Disposition: attachment; filename*="utf8\'\''.$fileName.'.xls');
        } else {
            header('Content-Disposition: attachment; filename="'.$fileName.'.xls');
        }
        header('Content-Type: application/vnd.ms-excel;');
        header('Cache-Control: max-age=0');
        // 输出下载文件
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
}